rm(list = ls())
# Desactivar la notación científica para facilitar la lectura de números grandes
options(scipen = 999)
library(readr)      # Para lectura eficiente de archivos CSV
library(readxl)     # Para lectura de archivos Excel
library(stringr)    # Para manipulación avanzada de cadenas de texto
library(dplyr)      # Para manipulación y agregación de datos
library(writexl)    # Para exportar resultados a Excel
I <- read.csv("C:/Users/maria.marino/OneDrive - COMISION DE REGULACIÓN DE COMUNICACIONES/CRC/Fraude/Bases/Script a/FT_5_2_ING 2.csv", sep = ";", dec = ",")
Ta <- read.csv("C:/Users/maria.marino/OneDrive - COMISION DE REGULACIÓN DE COMUNICACIONES/CRC/Fraude/Bases/Script a/FT_5_2_TRAF 1.csv", sep = ";", dec = ",")
C <- read.csv("C:/Users/maria.marino/OneDrive - COMISION DE REGULACIÓN DE COMUNICACIONES/CRC/Fraude/Bases/Script a/codigoscortos (1).csv", sep = ";", dec = ",")
C <- C %>%
rename(
CD = CODIGO,
AS = EMPRESA
)
C1 <- select(C, CD, AS)
I$MES <- (I$TRIMESTRE - 1) * 3 + I$MES_DEL_TRIMESTRE
I$FECHA   <- paste0(I$ANNO, "-", sprintf("%02d", I$MES))
I <- I %>%
rename(
IT = INGRESOS_PRST_SMS,
MC = MDN_CODIGO,
CD = CODIGO_CORTO
)
I <- select(I, FECHA, ID_EMPRESA, EMPRESA, CD, MC, IT)
Ta$MES <- (Ta$TRIMESTRE - 1) * 3 + Ta$MES_DEL_TRIMESTRE
Ta$FECHA   <- paste0(Ta$ANNO, "-", sprintf("%02d", Ta$MES))
Ta <- Ta %>%
rename(
TT = NUM_MENS_SMS_TRAFICO_TERMINADO,
TO = NUM_MENS_SMS_TRAFICO_ORIGINADO,
MC = MDN_CODIGO,
CD = CODIGO_CORTO
)
Ta <- select(Ta, FECHA, ID_EMPRESA, EMPRESA, CD, MC, TT, TO)
d <- merge(I, Ta, by = c("FECHA", "ID_EMPRESA", "EMPRESA", "CD", "MC"), all = TRUE)
d <- merge(d, C1, by = "CD", all.x = TRUE)
d$CD <- as.factor(d$CD)
d$ID_EMPRESA <- as.factor(d$ID_EMPRESA)
d <- d %>%
mutate(
EMPRESA = recode(EMPRESA,
"COMUNICACION CELULAR S A COMCEL S A"              = "COMCEL",
"COLOMBIA TELECOMUNICACIONES S.A. E.S.P."          = "MOVISTAR",
"COLOMBIA MOVIL S.A. E.S.P."                       = "TIGO",
"COLOMBIA MOVIL  S.A ESP"                          = "TIGO",
"PARTNERS TELECOM COLOMBIA SAS"                    = "PTC",
"VIRGIN MOBILE COLOMBIA S.A.S."                    = "VIRGIN",
"EMPRESA DE TELECOMUNICACIONES DE BOGOTA S.A. ESP" = "ETB",
"ALMACENES EXITO INVERSIONES S.A.S."               = "EXITO",
"SETROC MOBILE GROUP SAS"                          = "SETROC",
"AVANTEL S.A.S."                                   = "AVANTEL",
"AVANTEL S.A.S"                                    = "AVANTEL",
"LOGISTICA FLASH COLOMBIA S.A.S"                   = "FLASH",
"LOGISTICA EN TELECOMUNICACIONES S.A.S."           = "FLASH",
"SUMA MOVIL S.A.S."                                = "SUMA",
"LOV TELECOMUNICACIONES SAS"                       = "LOV",
"LIWA S.A.S. E.S.P."                               = "LIWA",
"PLINTRON COLOMBIA S.A.S"                          = "PLINTRON",
"PLINTRON COLOMBIA SAS"                            = "PLINTRON",
"ITEC SOLUTIONS SAS"                               = "ITEC",
"INSIDENET SAS"                                    = "INSIDENET",
"CELLVOZ COLOMBIA SERVICIOS INTEGRALES S A E S P"  = "CELLVOZ",
"CONREDES IM SAS"                                  = "CONREDES"
))
d1 <- filter(d, IT > 0)
d1$T <- d1$TT + d1$TO
d2 <- filter(d1, T > 0)
d2$p <- d2$IT / d2$T
d2 <- d2 %>%
group_by(FECHA, CD) %>%
mutate(n = n()) %>%
group_by(CD) %>%
mutate(N = max(n)) %>%
ungroup()
d3 <- filter(d2, N > 1)
d4 <- d3 %>%
filter(AS != "NINGUNO") %>%
group_by(FECHA, ID_EMPRESA, EMPRESA, AS) %>%
summarise(
T  = sum(T, na.rm = TRUE),
IT = sum(IT, na.rm = TRUE),
.groups = 'drop'
) %>%
mutate(p = IT / T)
d4 <- d4 %>%
mutate(
AS = recode(AS,
"PARTNERS TELECOM COLOMBIA SAS"           = "PTC",
"COMUNICACION CELULAR S.A."                = "COMCEL",
"COLOMBIA TELECOMUNICACIONES S.A. E.S.P."  = "MOVISTAR",
"COLOMBIA MOVIL S.A. E.S.P."               = "TIGO",
"PARTNERS TELECOM COLOMBIA S.A.S"           = "PTC"
)
) %>%
mutate(f = if_else(EMPRESA == AS, 1, 0)) %>%
filter(f == 0) %>%
select(-f)
R1 <- lm(log(T) ~ log(p) + FECHA + EMPRESA + AS, data = d4)
save(R1, file = "a. Regresiones.RData")
save(d4, file = "a. BD.RData")
rm(list = ls())
options(scipen = 999)
# Cargar las librerías necesarias
library(readr)      # Para lectura eficiente de archivos CSV
library(readxl)     # Para lectura de archivos Excel
library(stringr)    # Para manipulación avanzada de cadenas de texto
library(dplyr)      # Para manipulación y agregación de datos
library(writexl)    # Para exportar resultados a Excel
# Cargar las bases de datos crudas
d <- read.csv("C:/Users/maria.marino/OneDrive - COMISION DE REGULACIÓN DE COMUNICACIONES/CRC/Fraude/Bases/EMPAQUETAMIENTO_MOVIL.csv", sep = ";", dec = ",")
Ta <- read.csv("C:/Users/maria.marino/OneDrive - COMISION DE REGULACIÓN DE COMUNICACIONES/CRC/Fraude/Bases/FT_5_2_TRAF 1.csv", sep = ";", dec = ",")
d$mes <- (d$TRIMESTRE - 1) * 3 + d$MES_DEL_TRIMESTRE
d$t   <- paste0(d$ANNO, "-", sprintf("%02d", d$mes))
d <- d %>%
filter(MODALIDAD_PAGO != "Prepago sin compra" & CANTIDAD_LINEAS > 0 & VALOR_FACTURADO_O_COBRADO > 0) %>%
mutate(
EMPRESA = recode(EMPRESA,
"COMUNICACION CELULAR S A COMCEL S A"              = "COMCEL",
"COLOMBIA TELECOMUNICACIONES S.A. E.S.P."          = "MOVISTAR",
"COLOMBIA MOVIL S.A. E.S.P."                       = "TIGO",
"COLOMBIA MOVIL  S.A ESP"                          = "TIGO",
"PARTNERS TELECOM COLOMBIA SAS"                    = "PTC",
"VIRGIN MOBILE COLOMBIA S.A.S."                    = "VIRGIN",
"EMPRESA DE TELECOMUNICACIONES DE BOGOTA S.A. ESP" = "ETB",
"ALMACENES EXITO INVERSIONES S.A.S."               = "EXITO",
"SETROC MOBILE GROUP SAS"                          = "SETROC",
"AVANTEL S.A.S."                                   = "AVANTEL",
"AVANTEL S.A.S"                                    = "AVANTEL",
"LOGISTICA FLASH COLOMBIA S.A.S"                   = "FLASH",
"LOGISTICA EN TELECOMUNICACIONES S.A.S."           = "FLASH",
"SUMA MOVIL S.A.S."                                = "SUMA",
"LOV TELECOMUNICACIONES SAS"                       = "LOV",
"LIWA S.A.S. E.S.P."                               = "LIWA",
"PLINTRON COLOMBIA S.A.S"                          = "PLINTRON",
"PLINTRON COLOMBIA SAS"                            = "PLINTRON",
"ITEC SOLUTIONS SAS"                               = "ITEC",
"INSIDENET SAS"                                    = "INSIDENET",
"CELLVOZ COLOMBIA SERVICIOS INTEGRALES S A E S P"  = "CELLVOZ",
"CONREDES IM SAS"                                  = "CONREDES"
),
serv = ID_SERVICIO_PAQUETE,
posp = -(ID_MODALIDAD_PAGO - 2)
)
d <- d %>%
group_by(ANNO, t, EMPRESA, serv, posp) %>%
summarise(
q  = sum(CANTIDAD_LINEAS, na.rm = TRUE),
vp = sum(VALOR_FACTURADO_O_COBRADO, na.rm = TRUE),
p  = vp / (q * 1000),
.groups = 'drop'
) %>%
group_by(t, serv) %>%
mutate(
M = sum(q, na.rm = TRUE),
s = q / M
) %>%
ungroup()
empresas_relevantes <- d %>%
filter(serv == 3) %>%
group_by(t, EMPRESA) %>%
summarise(v = sum(s) * 100, .groups = 'drop') %>%
group_by(EMPRESA) %>%
summarise(m = mean(v), .groups = 'drop') %>%
filter(m >= 1) %>%
pull(EMPRESA)
d <- d %>% filter(EMPRESA %in% empresas_relevantes)
d <- d %>%
mutate(
j = as.numeric(as.factor(paste(EMPRESA, serv, posp, sep = '-'))),
k = as.numeric(as.factor(paste(EMPRESA, serv, sep = '-')))
) %>%
group_by(ANNO, j) %>%
mutate(z1 = (sum(p) - p) / (n() - 1)) %>%
group_by(ANNO, k) %>%
mutate(z2 = (sum(p) - p) / (n() - 1)) %>%
group_by(ANNO, EMPRESA) %>%
mutate(z3 = (sum(p) - p) / (n() - 1)) %>%
group_by(j) %>%
mutate(z4 = (sum(p) - p) / (n() - 1)) %>%
group_by(t, EMPRESA) %>%
mutate(z5 = (sum(p) - p) / (n() - 1)) %>%
ungroup()
d <- filter(d, serv == 3)
Ta$MES <- (Ta$TRIMESTRE - 1) * 3 + Ta$MES_DEL_TRIMESTRE
Ta$t   <- paste0(Ta$ANNO, "-", sprintf("%02d", Ta$MES))
Ta <- Ta %>%
rename(
TT = NUM_MENS_SMS_TRAFICO_TERMINADO,
TO = NUM_MENS_SMS_TRAFICO_ORIGINADO,
MC = MDN_CODIGO,
CD = CODIGO_CORTO
)
Ta <- select(Ta, t, ID_EMPRESA, EMPRESA, CD, MC, TT, TO)
Ta$T <- Ta$TT + Ta$TO
Ta <- Ta %>%
mutate(
EMPRESA = recode(EMPRESA,
"COMUNICACION CELULAR S A COMCEL S A"              = "COMCEL",
"COLOMBIA TELECOMUNICACIONES S.A. E.S.P."          = "MOVISTAR",
"COLOMBIA MOVIL  S.A ESP"                          = "TIGO",
"PARTNERS TELECOM COLOMBIA SAS"                    = "PTC",
"VIRGIN MOBILE COLOMBIA S.A.S."                    = "VIRGIN",
"EMPRESA DE TELECOMUNICACIONES DE BOGOTA S.A. ESP" = "ETB",
"SETROC MOBILE GROUP SAS"                          = "SETROC",
"AVANTEL S.A.S"                                    = "PTC",
"SUMA MOVIL S.A.S."                                = "SUMA",
"LOV TELECOMUNICACIONES SAS"                       = "LOV",
"LIWA S.A.S. E.S.P."                               = "LIWA",
"PLINTRON COLOMBIA S.A.S"                          = "PLINTRON",
"PLINTRON COLOMBIA SAS"                            = "PLINTRON",
"ITEC SOLUTIONS SAS"                               = "ITEC",
"INSIDENET SAS"                                    = "INSIDENET",
"CELLVOZ COLOMBIA SERVICIOS INTEGRALES S A E S P"  = "CELLVOZ",
"CONREDES IM SAS"                                  = "CONREDES"
)) %>%
filter(EMPRESA %in% empresas_relevantes)
Ta1 <- Ta %>%
group_by(t, EMPRESA) %>%
summarise(T = sum(T), .groups = 'drop')
d <- merge(d, Ta1, by = c("t", "EMPRESA"), all.x = TRUE)
d <- d %>%
group_by(t, EMPRESA) %>%
mutate(w = sum(s)) %>%
ungroup() %>%
mutate(
W = s / w,
TA2P = W * T,
A2P = T / q
)
DATA <- d
save(DATA, file = "b. MDATA4.RData")
